# -*- coding: utf-8 -*-
'''
Created on Nov 24, 2013

@author: LONG HOANG GIANG
'''

# import MySQLdb as db
import pymysql as db
import sys

class MySQL():
    
    connection = None
    
    def __init__(self, host, user, passwd, database):
        self.openConnection(host, user, passwd, database)
            
    def openConnection(self, host, user, passwd, database):
        try:
            self.connection = db.connect(host, user, passwd, database, charset='utf8')
        except db.Error, e:
            print "Error %d: %s" % (e.args[0],e.args[1])
            sys.exit(1)
    
    def close(self):
        if (self.connection):
            self.connection.close()
    
    def commit(self):
        if (self.connection): 
            self.connection.commit()
            
    def execute(self, sql, *args):
        rows = None
        try:
            cursor = self.connection.cursor()
            if len(args) > 0 and ('%s' in sql):
                cursor.execute(sql, args[0])
            else:
                cursor.execute(sql)
            rows = cursor.fetchall()
            if len(rows) < 1:
                rows = None
            elif len(rows) == 1: 
                rows = rows[0]
            cursor.close()
        except db.Error, e:
            print "Error %d: %s" % (e.args[0],e.args[1])
        return rows;
    
    def insert(self, table, meta):
        if type(meta).__name__ != 'dict':
            print "Error: meta must be a dict"; return
        sql = "INSERT INTO `{0}`({1}) VALUES({2})".format(table, ', '.join(meta.keys()), ', '.join( ['%s'] * len(meta.values())))
        self.execute(sql, meta.values())
    
    def count(self, table, key='*'):
        sql = "SELECT COUNT({_k}) FROM {_t}".format(_k=key, _t=table)
        rows = self.execute(sql)
        if len(rows) > 0: return rows[0]
        return 0  
    
    def select(self, table, key='*', where=None):
        if type(key).__name__ == 'list' or type(key).__name__ == 'tupple':
            sql = "SELECT {0} FROM `{1}`".format(', '.join(key), table)
        else:
            sql = "SELECT * FROM `{0}`".format(table)
        if type(where).__name__ == 'dict':
            if len(where.items()) > 0:
                sql += " WHERE {0}".format(' AND '.join([ '{0}=%s'.format(c) for c in where.keys() ]))
        rows = self.execute(sql, where.values()) if where != None and '%s' in sql else self.execute(sql)
        return rows
    
    def update(self, table, meta, where={}):
        if type(meta).__name__ != 'dict':
            print "Error: meta must be a dict"; return
        sql = "UPDATE `{0}` SET {1}".format(table, ', '.join([ '{0}=%s'.format(c) for c in meta.keys() ]))
        if len(where.items()) > 0:
            sql += " WHERE {0}".format(" AND ".join([ '{0}=%s'.format(c) for c in where.keys() ]))
        args = meta.values() + where.values() if type(where).__name__ == 'dict' else meta.values()
        self.execute(sql, args)
        
    def delete(self, table, where):
        if type(where).__name__ != 'dict':
            print "Error: where must be a dict"; return
        if len(where.items()) < 1:
            print "Error: condition must not empty"; return
        sql = "DELETE FROM `{0}` WHERE {1}".format(table, ' AND '.join( ['{0}=%s'.format(c) for c in where.keys()] ))
        self.execute(sql, where.values())
    
    def version(self):
        v = self.execute("SELECT VERSION()")
        return v[0]
          
    def checkTableExists(self, tableName):
        rows = self.execute("SHOW TABLES;")
        for row in rows:
            if tableName == row:
                return True
        return False
